﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Diagnostics;
using CC.Common;

namespace CC.MdbAccess
{
    public class MdbHelper
    {
        public delegate void ShowProgress(int percentage);
        public static string ErrMsg { get; private set; }

        /// <summary>
        /// 含数据库密码的连接
        /// </summary>
        /// <param name="file"></param>
        /// <param name="dbPassword"></param>
        /// <returns></returns>
        public static OleDbConnection CreateConnection(string file, string dbPassword = "")
        {
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + file;
            if (!string.IsNullOrEmpty(dbPassword))
                strCon += ";Jet OleDb:DataBase Password=" + dbPassword;
            return new OleDbConnection(strCon);
        }
        /// <summary>
        /// 含数据库密码和用户密码的连接
        /// </summary>
        /// <param name="file"></param>
        /// <param name="filePassword"></param>
        /// <returns></returns>
        public static OleDbConnection CreateConnection(string file, string dbPassword, string user, string password)
        {
            string strCon = $" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source={file}" +
                $";Jet OleDb:DataBase Password={dbPassword}" +
                $";Persist Security Info=true;password={password};user id={user}";
            return new OleDbConnection(strCon);
        }
        public static int ExecuteNonQuery(OleDbConnection connection, string sql, OleDbParameter[] parameters = null, bool closeWhileFinish = true)
        {
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            if (parameters != null && parameters.Length > 0) cmd.Parameters.AddRange(parameters);
            if (connection.State == ConnectionState.Closed) connection.Open();
            int n = cmd.ExecuteNonQuery();
            if (closeWhileFinish) connection.Close();
            return n;

        }
        public static int ExecuteIdentity(OleDbConnection connection, string sql, OleDbParameter[] parameters = null, bool closeWhileFinish = true)
        {
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            if (parameters != null && parameters.Length > 0) cmd.Parameters.AddRange(parameters);
            if (connection.State == ConnectionState.Closed) connection.Open();
            cmd.CommandText = sql + @";select @@identity";
            int value = Int32.Parse(cmd.ExecuteScalar().ToString());
            if (closeWhileFinish) connection.Close();
            return value;
        }
        /// <summary>
        /// 用事务执行多指令
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sqlCommands">要批量执行的指令</param>
        /// <param name="parameters">指令中用到的参数。默认无参。有参时数量需要与指令数量一一对应</param>
        public static void DbTransaction(OleDbConnection connection, List<string> sqlCommands, List<OleDbParameter[]> parameters = null)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = connection;
            connection.Open();
            OleDbTransaction transaction = connection.BeginTransaction();
            cmd.Transaction = transaction;
            if (parameters != null && sqlCommands.Count != parameters.Count)
            {
                throw new Exception("执行事务：SQL指令和参数数量不匹配！");
            }
            int count = 0;
            try
            {
                for (int i = 0; i < sqlCommands.Count; i++)
                {
                    cmd.CommandText = sqlCommands[i];
                    if (parameters != null && parameters.Count > i)
                        cmd.Parameters.AddRange(parameters[i]);
                    count += cmd.ExecuteNonQuery();
                }

                transaction.Commit();

            }
            catch (Exception)
            {
                cmd.Transaction.Rollback();
            }
            finally
            {
                connection.Close();
            }

        }
        /// <summary>
        /// 查询并返回DataSet
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(OleDbConnection conn, string sql)
        {
            DataSet ds = new DataSet();
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
            da.Fill(ds, "ds");
            conn.Close();
            return ds;
        }

        #region Application
        /// <summary>
        /// 清除数据
        /// </summary>
        /// <param name="odcConnection"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static int ClearData(OleDbConnection conn, string tableName)
        {

            string sql = $" TRUNCATE TABLE {tableName}";
            return ExecuteNonQuery(conn, sql);
        }

        /// <summary>
        /// 读取数据库全部数据，并输出到DataTable
        /// </summary>
        /// <param name="odcConnection">数据库连接</param>
        /// <param name="tableName">表名</param>
        /// <param name="dt">输出的数据</param>
        /// <returns></returns>
        public static DataTable ReadAllData(OleDbConnection odcConnection, string tableName)
        {
            var dt = new DataTable();
            odcConnection.Open();
            //建立SQL查询   
            OleDbCommand odCommand = odcConnection.CreateCommand();
            //3、输入查询语句 C#操作Access之读取mdb  
            odCommand.CommandText = "select * from " + tableName;
            //建立读取   
            OleDbDataReader odrReader = odCommand.ExecuteReader();
            //查询并显示数据   
            int size = odrReader.FieldCount;
            for (int i = 0; i < size; i++)
            {

                dt.Columns.Add(odrReader.GetName(i), typeof(object));
            }
            while (odrReader.Read())
            {
                var dr = dt.NewRow();
                for (int i = 0; i < size; i++)
                {
                    string cName = odrReader.GetName(i);
                    if (odrReader[cName] != DBNull.Value)
                    {
                        dr[cName] = odrReader[cName];
                    }

                    //  dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }
                dt.Rows.Add(dr);
            }
            //关闭连接 C#操作Access之读取mdb  
            odrReader.Close();
            odcConnection.Close();
            return dt;
        }
        /// <summary>
        /// 读取指定列的内容
        /// </summary>
        /// <param name="odcConnection">数据库连接</param>
        /// <param name="tableName">表名</param>
        /// <param name="columns">指定的列清单</param>
        /// <param name="dt">输出的数据表</param>
        /// <returns>是否成功</returns>
        public static DataTable ReadDataByColumns(OleDbConnection odcConnection, string tableName, string[] columns)
        {
            var dt = new DataTable();
            DataRow dr;

            //2、打开连接 C#操作Access之按列读取mdb  
            odcConnection.Open();
            //建立SQL查询   
            OleDbCommand odCommand = odcConnection.CreateCommand();
            //3、输入查询语句   
            string strColumn = "";
            for (int i = 0; i < columns.Length; i++)
            {
                strColumn += columns[i].ToString() + ",";
            }
            strColumn = strColumn.TrimEnd(',');
            odCommand.CommandText = "select " + strColumn + " from " + tableName;
            //建立读取 C#操作Access之按列读取mdb  
            OleDbDataReader odrReader = odCommand.ExecuteReader();
            //查询并显示数据 C#操作Access之按列读取mdb  
            int size = odrReader.FieldCount;
            for (int i = 0; i < size; i++)
            {
                DataColumn dc;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }
            while (odrReader.Read())
            {
                dr = dt.NewRow();
                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }
                dt.Rows.Add(dr);
            }
            //关闭连接 C#操作Access之按列读取mdb  
            odrReader.Close();
            odcConnection.Close();
            return dt;
        }
        /// <summary>
        /// 指定表是否存在。
        /// 很简单，执行一个查询指令，不出异常就存在
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static bool ExistTable(OleDbConnection conn, string tableName)
        {
            string sql = $"SELECT COUNT(*) FROM {tableName}";
            try
            {
                ExecuteNonQuery(conn, sql);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        /// 创建一个类型和DataTable一致的空表
        /// 创建的表的列类型有问题，不能用
        /// </summary>
        /// <param name="dt">数据表</param>
        public static void CreateTable(OleDbConnection conn, string tableName, DataTable dt)
        {
            string sql = "create table " + tableName;
            string tableAttribute = "";
            if (conn.State == ConnectionState.Closed) conn.Open();
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    tableAttribute = tableAttribute + dt.Columns[i].ColumnName + " " + GetType(dt.Columns[i].DataType.ToString()) + ",";
            //}
            //sql = sql + "(Num AUTOINCREMENT," + tableAttribute + "CONSTRAINT table_PK PRIMARYKEY(Num));";
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                tableAttribute = tableAttribute + dt.Columns[i].ColumnName + " " + GetType(dt.Columns[i].DataType.ToString());
                if (i < dt.Columns.Count - 1)
                {
                    tableAttribute = tableAttribute + ",";
                }
            }
            sql = sql + "(" + tableAttribute + ");";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static int DatatableToMdb2(OleDbConnection conn, string tblName, DataTable dataTable)
        {
            //第一步：获取数据库配置信息
            //String connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
            //第二步：构建SqlCommand查询语句
            OleDbCommand command = new OleDbCommand($"select * from {tblName};");
            command.Connection = conn;
            //command.Connection = new SqlConnection(connStr);
            //第三步：创建SqlDataAdapter
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            //第四步：创建DataSet和DataTable
            DataSet dataSet = new DataSet();
            //DataTable dataTable = new DataTable();
            //第五步：填充数据
            //adapter.Fill(dataTable);
            dataSet.Tables.Add(dataTable);
            ////修改第一行数据中的姓名为小红，并将其性别改为女
            dataTable.Rows[0]["AntType"] = "KBT65DP";
            //dataTable.Rows[0]["ssex"] = "女";
            ////删除第二行数据
            //dataTable.Rows[1].Delete();
            ////构建一个新的行，并添加到表中去
            //dataTable.Rows.Add(new object[] { "小明", 108, 18, "男" });
            //创建SqlCommandBuilder对象，并绑定一个SqlDataAdapter对象
            OleDbCommandBuilder scb = new OleDbCommandBuilder(adapter);
            //打印输出SqlCommandBuilder对象的增删改sql命令语句
#if DEBUG
            Debug.WriteLine("SqlCommandBuilder实例的Insert命令： " + scb.GetInsertCommand().CommandText);
            Debug.WriteLine("SqlCommandBuilder实例的Delete命令： " + scb.GetDeleteCommand().CommandText);
            Debug.WriteLine("SqlCommandBuilder实例的Update命令： " + scb.GetUpdateCommand().CommandText);
#endif

            //将有变动的行同步到数据库中
            int count = adapter.Update(dataTable.GetChanges());
            //保存修改
            dataTable.AcceptChanges();
            ////下面是一个遍历输出datatable中的数据
            //foreach (DataTable table in dataSet.Tables)
            //{
            //    foreach (DataRow row in table.Rows)
            //    {
            //        Console.WriteLine(row[0] + ", " + row[1] + ", " + row[2] + ", " + row[3]);
            //    }
            //}
            return count;
        }

        /// <summary>
        /// 将datatable导入对应名字的表中
        /// 表内需要有一行数据，目的是给SqlDataAdapter用
        /// </summary>
        /// <param name="tblName"></param>
        /// <param name="dt"></param>
        public static void DatatableToMdb(OleDbConnection conn, string tblName, DataTable dt)
        {

            string strCom = string.Format("select * from {0}", tblName);
            OleDbDataAdapter da = new OleDbDataAdapter(strCom, conn);
            if (conn.State == ConnectionState.Closed) conn.Open();

            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(dt);
            da.Fill(dataSet, tblName);
            foreach (DataRow dR in dt.Rows)
            {
                DataRow dr = dataSet.Tables[tblName].NewRow();
                dr.ItemArray = dR.ItemArray;//行复制
                dataSet.Tables[tblName].Rows.Add(dr);
            }
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);//这里的CommandBuilder对象一定不要忘了,一般就是写在DataAdapter定义的后面
#if DEBUG
            Debug.WriteLine("IntertCommand:" + cb.GetInsertCommand().CommandText);
#endif
            //cb.QuotePrefix = "[";
            //cb.QuoteSuffix = "]";
            da.Update(dataSet, tblName);
            conn.Close();
        }
        /// <summary>
        /// 获取创建mdb表格的属性字段类型
        /// </summary>
        /// <param name="datatype"></param>
        /// <returns></returns>
        private static string GetType(string datatype)
        {
            switch (datatype)//匹配类型选择
            {
                case "System.DateTime":
                    return "DateTime";
                case "System.Double":
                    return "Double";
                case "System.Int32":
                case "System.Int16":
                case "System.Int64":
                    return "Int";
                default:
                case "System.String":
                    return "TEXT(300)";
            }
        }
        #endregion

        #region 高级方法



        /// <summary>
        /// 构造带参数的Insert语句,
        /// 如果ID列是GUID，则会自动产生ID
        /// </summary>
        /// <typeparam name="T">要带入的类。类属性名=数据库表列名</typeparam>
        /// <param name="obj">类实体</param>
        /// <param name="paras">输出参数 </param>
        /// <param name="idColName">ID列的名称，在插入语句中，需要对ID列进行特殊处理</param>
        /// <param name="useGuid">ID列是否适用GUID。如果使用GUID，则会生成新GUID</param>
        /// <param name="excludeProperties">要排除的属性。这些属性不会被写入，ID列不要列入排除清单</param>
        /// <returns>SQL语句，和SQL参数</returns>
        public static string BuildInsertCommand<T>(T obj, string tableName, out OleDbParameter[] paras)
        {
            var type = typeof(T);
            //排除不输出的列的属性清单
            PropertyInfo[] properties = type.GetProperties().Where(p => !DataColumnAttribute.IsColumnIgnore(p)).ToArray();
            string fields = "";
            string values = "";
            //构造参数清单
            List<OleDbParameter> list = new List<OleDbParameter>();

            //构造sql语语句
            foreach (var p in properties)
            {
                if (DataColumnAttribute.IsAutoIncrement(p) && p.PropertyType != typeof(Guid))
                {//忽略自增但不是GUID的列
                    continue;
                }
                var value = p.GetValue(obj, null);
                if (value == null) continue;
                //string cName = propNameColNames.ContainsKey(p.Name) ? propNameColNames[p.Name] : p.Name;
                string cName = DataColumnAttribute.GetColumnName(p);
                if (string.IsNullOrEmpty(cName)) cName = p.Name;
                fields += "," + cName;
                values += ",@" + p.Name;
                if (!DataColumnAttribute.IsAutoIncrement(p))
                {
                    list.Add(new OleDbParameter { ParameterName = "@" + p.Name, Value = p.GetValue(obj, null) });
                }
                else if (p.PropertyType == typeof(Guid))//如果自增列是GUID，则生成新GUID
                {
                    string guid = Guid.NewGuid().ToString();
                    list.Add(new OleDbParameter { ParameterName = "@" + p.Name, Value = guid });
                }
            }
            string sql = $"INSERT INTO {tableName}(" + fields.Substring(1) + ") VALUES(" + values.Substring(1) + ")";


            paras = list.ToArray();
            return sql;
        }
        /// <summary>
        /// 构造带参数的Update语句
        /// UPDATE 
        /// </summary>
        /// <typeparam name="T">要带入的类。类属性名=数据库表列名</typeparam>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <param name="idColName"></param>
        /// <param name="paras"></param>
        /// <param name="excludeProperties">此处ID列作为条件筛选，不可剔除</param>
        /// <returns></returns>
        public static string BuildUpdateCommand<T>(T obj, string tableName, out OleDbParameter[] paras)
        {
            var type = typeof(T);
            //排除自增ID列以及不输出的列的属性清单
            PropertyInfo[] properties = type.GetProperties().Where(p => !DataColumnAttribute.IsColumnIgnore(p)).ToArray();

            Dictionary<string, string> propNameColNames = new Dictionary<string, string>();
            //用一个字典暂存属性名和列名的关系，在设置行值时要用
            foreach (var prop in properties)
            {
                string colName = DataColumnAttribute.GetColumnName(prop);
                if (!string.IsNullOrEmpty(colName))
                {
                    propNameColNames.Add(prop.Name, colName);
                }
            }
            //查找ID列
            var idPi = type.GetProperties().FirstOrDefault(p => DataColumnAttribute.IsIdColumn(p));
            if (idPi == null)
            {
                throw new Exception("构建UPDATE语句：缺少ID列，请使用DataColumnAttribute属性设置ID列");
            }
            string idColName = DataColumnAttribute.GetColumnName(idPi);
            if (propNameColNames.ContainsKey(idColName)) idColName = propNameColNames[idColName];

            //构造sql语语句 UPDATE {tableName} SET field1=@value1, field2=@value2 WHERE ID=@id
            string sql = $"UPDATE {tableName} SET ";
            string fieldValues = "";//构造 ,field1=@field1, field2=@field2....
            foreach (var p in properties)
            {
                string cName = propNameColNames.ContainsKey(p.Name) ? propNameColNames[p.Name] : p.Name;
                fieldValues += $",{cName}=@{cName} ";
            }
            sql += fieldValues.Substring(1) + $" WHERE {idColName}=@{idColName}";

            var list = new List<OleDbParameter>();
            foreach (var p in properties)
            {

                object value = p.GetValue(obj, null);
                if (value != null)
                {
                    var ans = value.GetType();//如果是GUID，则用toString()否则无法筛选
                    if (ans.Name == "Guid") list.Add(new OleDbParameter { ParameterName = "@" + p.Name, Value = value.ToString() });
                    else
                    {
                        list.Add(new OleDbParameter { ParameterName = "@" + p.Name, Value = value });
                    }
                }
                else
                {
                    list.Add(new OleDbParameter { ParameterName = "@" + p.Name, Value = "" });
                }
            }
            paras = list.ToArray();
            return sql;
        }
        /// <summary>
        /// 高级插入方法：传入类实例
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connStr"></param>
        /// <param name="obj">类实例</param>
        /// <param name="tableName"></param>
        /// <param name="idColName">ID列名，要求与类的ID字段名称相同</param>
        /// <param name="useGuid">是否使用GUID.如果使用GUID，则在此生成GUID</param>
        /// <param name="excludeProperties">要排除的字段，这些属性不会被写入，ID列不要列入排除清单</param>
        /// <returns>返回刚插入的行ID</returns>
        public static int InsertObject<T>(OleDbConnection conn, T obj, string tableName, bool closeWhileFinish = true)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            string sql = BuildInsertCommand(obj, tableName, out OleDbParameter[] paras);
            //sql += ";select last_insert_rowid()";//返回新增的行ID
            return ExecuteNonQuery(conn, sql, paras, closeWhileFinish);
        }
        /// <summary>
        /// 更新一个实体到数据库
        /// 要求实体有用DataColumnAttribute标识的ID字段，
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="obj"></param>
        /// <param name="tableName"></param>
        /// <param name="closeWhileFinish">更新完成后是否关闭连接</param>
        /// <returns></returns>
        public static int UpdateObject<T>(OleDbConnection conn, T obj, string tableName, bool closeWhileFinish = true)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            string sql = BuildUpdateCommand(obj, tableName, out OleDbParameter[] paras);
            return ExecuteNonQuery(conn, sql, paras, closeWhileFinish);
        }
        /// <summary>
        /// 批量插入数据
        /// 要求实体有用DataColumnAttribute标识的ID字段，程序更新除ID外其他字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        /// <param name="showProgress">显示更新进程的委托</param>
        /// <returns></returns>
        public static int BatchInsert<T>(OleDbConnection conn, string tableName, IEnumerable<T> list, ShowProgress showProgress = null)
        {//用事务方式失败，改用普通插入试试
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            if (conn.State == ConnectionState.Closed) conn.Open();
            int count = 0;
            #region 普通插入方式：成功！
#if DEBUG
            Debug.WriteLine("Writing " + tableName);
#endif
            float total = list.Count();
            foreach (var item in list)
            {
                count += InsertObject(conn, item, tableName, false);
                //可选显示进度
                if (showProgress != null && count % 100 == 0)
                {
                    showProgress((int)(count/total*100));
                }
#if DEBUG
                if (count % 100 == 0)
                    Debug.WriteLine("    " + count);
#endif
            }
            conn.Close();
            #endregion
            #region 事务方式批量插入，会越来越慢
            //            OleDbTransaction transaction = conn.BeginTransaction();
            //            cmd.Transaction = transaction;
            //            try
            //            {
            //                foreach (var item in list)
            //                {
            //                    string sql = BuildInsertCommand(item, tableName, out var paras);
            //                    cmd.CommandText = sql;
            //                    cmd.Parameters.AddRange(paras);
            //                    count += cmd.ExecuteNonQuery();
            //#if DEBUG
            //                    if (count % 100 == 0)
            //                        Debug.WriteLine("    " + count);
            //#endif
            //                }
            //                transaction.Commit();

            //            }
            //            catch (Exception ex)
            //            {
            //                count = 0;
            //                ErrMsg = ex.Message + "\r\n" + ex.StackTrace;
            //                cmd.Transaction.Rollback();
            //            }
            //            finally
            //            {
            //                conn.Close();
            //            }
            #endregion
            return count;
        }

        /// <summary>
        /// 批量改写数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="list"></param>
        /// <param name="showProgress"></param>
        /// <returns></returns>
        public static int BatchUpdate<T>(OleDbConnection conn, string tableName, IEnumerable<T> list, ShowProgress showProgress = null)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            if (conn.State == ConnectionState.Closed) conn.Open();
            int count = 0;
            
#if DEBUG
            Debug.WriteLine("Writing " + tableName);
#endif
            float total = list.Count();
            foreach (var item in list)
            {
                count += UpdateObject(conn, item, tableName, false);
                //可选显示进度
                if (showProgress != null && count % 100 == 0)
                {
                    showProgress((int)(count / total * 100));
                }
#if DEBUG
                if (count % 100 == 0)
                    Debug.WriteLine("    " + count);
#endif
            }
            conn.Close();
            return count;
        }

        #endregion
    
    }
}
